package com.chuan.icons;

import java.util.HashMap;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class LiteDB {
	private static HashMap<String, String> SQL_TABLE = new HashMap<String, String>();
	private final static String DEFAULT_DB_PATH = Constants.DB_PATH;

	public static SQLiteDatabase mDB = null;
	private static String mPath = DEFAULT_DB_PATH;

	private static Boolean createTable(String sql) {
		try {
			mDB.execSQL(sql);
		} catch (Exception e) {
			Log.e(Constants.LOG_FLAG, "Create table fail: " + e.toString());
			return false;
		}
		return true;
	}

	private static Boolean isTableExist(String tablename) {
		String sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='"
				+ tablename + "'";
		Cursor cur = mDB.rawQuery(sql, null);
		if (cur.moveToFirst()) {
			cur.close();
			return true;
		}

		cur.close();
		return false;
	}

	public static void openLiteDB() {
		if (mDB != null && mDB.isOpen()) {
			return;
		}

		mDB = SQLiteDatabase.openOrCreateDatabase(mPath, null);
		SQL_TABLE
				.put(
						Constants.TABLE_ACCOUNTS,
						"CREATE TABLE accounts(a_id INTEGER PRIMARY KEY AUTOINCREMENT, user TEXT, site TEXT, parameter TEXT)");
		SQL_TABLE
				.put(
						Constants.TABLE_FRIENDS,
						"CREATE TABLE friends(f_id TEXT, a_id INTEGER, parameter TEXT, PRIMARY KEY(f_id, a_id))");
		SQL_TABLE
				.put(
						Constants.TABLE_RELATIONSHIP,
						"CREATE TABLE relationship(f_id TEXT, c_id INTEGER, a_id INTEGER, relationship TEXT, PRIMARY KEY(f_id, c_id, a_id))");
		SQL_TABLE
				.put(
						Constants.TABLE_CONTACTS,
						"CREATE TABLE contacts(c_id INTEGER, name TEXT, mail TEXT, parameter TEXT, PRIMARY KEY(c_id))");

		SQL_TABLE.put(Constants.TABLE_PICS,
				"CREATE TABLE pics(url TEXT, data BLOG, primary key(url))");

		for (Map.Entry<String, String> entry : SQL_TABLE.entrySet()) {
			if (!isTableExist((String) entry.getKey())) {
				createTable((String) entry.getValue());
			}
		}
	}

	public static long put_pic(String url, byte[] data) {
		ContentValues cv = new ContentValues();
		cv.put("url", url);
		cv.put("data", data);
		mDB.delete(Constants.TABLE_PICS, "url=?", new String[] { url });
		return mDB.insert(Constants.TABLE_PICS, null, cv);
	}

	public static byte[] get_pic(String url) {
		String sql = "SELECT url, data FROM pics WHERE url='" + url + "'";
		byte[] data = null;
		Cursor cur = mDB.rawQuery(sql, null);
		if (cur.moveToFirst()) {
			data = cur.getBlob(1);
		}
		cur.close();
		return data;
	}

	public static int delete_pic(String url) {
		return mDB.delete(Constants.TABLE_PICS, "url=?", new String[] { url });
	}

	static public void closeLiteDB() {
		mDB.close();
	}

	private static Boolean isTableEmpty(String tablename) {
		String sql = "SELECT * FROM " + tablename;
		Cursor cur = mDB.rawQuery(sql, null);

		if (cur.moveToFirst()) {
			cur.close();
			return false;
		} else {
			cur.close();
			return true;
		}
	}

	public static Boolean isDBContactsEmpty() {
		return isTableEmpty(Constants.TABLE_CONTACTS);
	}

	public static long putDBContacts(long c_id, String name, String mail,
			String parameter) {
		ContentValues cv = new ContentValues();
		cv.put("c_id", c_id);
		cv.put("name", name);
		cv.put("mail", mail);
		cv.put("parameter", parameter);
		return mDB.replace(Constants.TABLE_CONTACTS, null, cv);
	}

	void getDBContacts() {
		return;
	}

	public static JSONArray getPairedFriends(String user, String site,
			int friendstype) {
		String tablename = "friends, relationship, accounts";
		String[] columns = new String[] { "friends.f_id, friends.parameter, relationship.c_id" };
		String selection = "friends.f_id=relationship.f_id AND friends.a_id=relationship.a_id AND friends.a_id=accounts.a_id AND accounts.user=? AND accounts.site=? AND relationship.relationship=?";
		String[] selectionArgs = new String[] { user, site,
				Integer.toString(friendstype) };
		Cursor cur = mDB.query(tablename, columns, selection, selectionArgs,
				null, null, null);

		JSONArray friends = new JSONArray();

		if (!cur.moveToFirst()) {
			cur.close();
			return friends;
		}

		do {
			JSONObject obj = new JSONObject();
			try {
				obj.put("f_id", cur.getString(0));
				obj.put("parameter", new JSONObject(cur.getString(1)));
				obj.put("c_id", cur.getShort(2));
				friends.put(obj);
			} catch (JSONException e) {
			}
		} while (cur.moveToNext());

		cur.close();

		return friends;
	}

	// TODO: fix it.
	public static long getFriendsCount(String user, String site) {
		String tablename = "friends, accounts";
		String[] columns = new String[] { "COUNT(friends.f_id)" };
		String selection = "friends.a_id=accounts.a_id AND accounts.user=? AND accounts.site=?";
		String[] selectionArgs = new String[] { user, site };
		Cursor cur = mDB.query(tablename, columns, selection, selectionArgs,
				null, null, null);

		if (!cur.moveToFirst()) {
			cur.close();
			return 0;
		}

		long cnt = cur.getLong(0);
		cur.close();
		return cnt;
	}

	public static JSONArray getAccounts() {
		JSONArray ary = new JSONArray();
		String[] columns = new String[] { "a_id, user, site" };
		Cursor cur = mDB.query(Constants.TABLE_ACCOUNTS, columns, null, null,
				null, null, null);
		if (!cur.moveToFirst()) {
			cur.close();
			return ary;
		}

		do {
			JSONObject obj = new JSONObject();
			try {
				// obj.put("a_id", cur.getString(cur.getColumnIndex("a_id")));
				obj.put("user", cur.getString(cur.getColumnIndex("user")));
				obj.put("site", cur.getString(cur.getColumnIndex("site")));

				ary.put(obj);
			} catch (JSONException e) {
				continue;
			}
		} while (cur.moveToNext());

		cur.close();
		return ary;
	}

	public static int getAccountID(String user, String site) {
		String[] columns = new String[] { "a_id, user, site, parameter" };
		String selection = "user=? AND site=?";
		String[] selectionArgs = new String[] { user, site };
		Cursor cur = mDB.query(Constants.TABLE_ACCOUNTS, columns, selection,
				selectionArgs, null, null, null);

		if (!cur.moveToFirst()) {
			cur.close();
			return -1;
		}

		int a_id = -1;
		a_id = cur.getInt(cur.getColumnIndex("a_id"));

		cur.close();
		return a_id;
	}

	public static JSONObject getAccountDetail(String user, String site) {
		String[] columns = new String[] { "a_id, user, site, parameter" };
		String selection = "user=? AND site=?";
		String[] selectionArgs = new String[] { user, site };
		Cursor cur = mDB.query(Constants.TABLE_ACCOUNTS, columns, selection,
				selectionArgs, null, null, null);

		JSONObject obj = new JSONObject();
		if (!cur.moveToFirst()) {
			cur.close();
			return null;
		}

		try {
			obj.put("a_id", cur.getInt(cur.getColumnIndex("a_id")));
			obj.put("user", cur.getString(cur.getColumnIndex("user")));
			obj.put("site", cur.getString(cur.getColumnIndex("site")));
			obj.put("parameter", new JSONObject(cur.getString(cur
					.getColumnIndex("parameter"))));
		} catch (JSONException e) {
			cur.close();
			return null;
		}

		cur.close();
		return obj;
	}

	public static long addAccounts(String user, String site,
			JSONObject parameter) {
		ContentValues cv = new ContentValues();
		cv.put("user", user);
		cv.put("site", site);
		cv.put("parameter", parameter.toString());
		return mDB.insert(Constants.TABLE_ACCOUNTS, null, cv);
	}

	public static int deleteAccounts(String user, String site) {
		String whereClause = "user=? AND site=?";
		String[] whereArgs = new String[] { user, site };
		return mDB.delete(Constants.TABLE_ACCOUNTS, whereClause, whereArgs);
	}

	public static long addFriends(String f_id, String user, String site,
			JSONObject parameter) {
		long a_id = -1;
		a_id = getAccountID(user, site);
		if (a_id == -1) {
			return -1;
		}

		ContentValues cv = new ContentValues();
		cv.put("f_id", f_id);
		cv.put("a_id", a_id);
		cv.put("parameter", parameter.toString());

		return mDB.insert(Constants.TABLE_FRIENDS, null, cv);
	}

	public static JSONArray getFriends(String user, String site) {
		int a_id = getAccountID(user, site);
		JSONArray friends = new JSONArray();
		if (a_id == -1) {
			return friends;
		}

		String[] columns = new String[] { "f_id, a_id, parameter" };
		String selection = "a_id=?";
		String[] selectionArgs = new String[] { Integer.toString(a_id) };
		Cursor cur = mDB.query(Constants.TABLE_FRIENDS, columns, selection,
				selectionArgs, null, null, null);
		if (!cur.moveToFirst()) {
			cur.close();
			return friends;
		}

		do {
			JSONObject obj = new JSONObject();
			try {
				obj.put("f_id", cur.getString(cur.getColumnIndex("f_id")));
				obj.put("a_id", cur.getString(cur.getColumnIndex("a_id")));
				obj.put("parameter", new JSONObject(cur.getString(cur
						.getColumnIndex("parameter"))));

				friends.put(obj);
			} catch (JSONException e) {
				continue;
			}
		} while (cur.moveToNext());

		cur.close();
		return friends;
	}

	public static int deleteFriendsByAccount(String user, String site) {
		int a_id = getAccountID(user, site);
		if (a_id == -1) {
			return -1;
		}
		return mDB.delete(Constants.TABLE_FRIENDS, "a_id=?",
				new String[] { Integer.toString(a_id) });
	}

	public static long addRelationship(String user, String site, String f_id,
			int c_id, int type) {
		int a_id = -1;
		a_id = getAccountID(user, site);
		if (a_id == -1) {
			return -1;
		}

		ContentValues cv = new ContentValues();
		cv.put("f_id", f_id);
		cv.put("a_id", a_id);
		cv.put("c_id", c_id);
		cv.put("relationship", Integer.toString(type));

		return mDB.insert(Constants.TABLE_RELATIONSHIP, null, cv);
	}

	public static int deleteRelationshipByAccount(String user, String site) {
		int a_id = getAccountID(user, site);
		if (a_id == -1) {
			return -1;
		}
		return mDB.delete(Constants.TABLE_RELATIONSHIP, "a_id=?",
				new String[] { Integer.toString(a_id) });
	}

	public static int deleteSpecifyRelationship(String user, String site,
			String f_id, int c_id, int relationship) {
		int a_id = getAccountID(user, site);
		if (a_id == -1) {
			return -1;
		}

		return mDB.delete(Constants.TABLE_RELATIONSHIP,
				"a_id=? AND f_id=? AND c_id=? AND relationship=?",
				new String[] { Integer.toString(a_id), f_id,
						Integer.toString(c_id), Integer.toString(relationship) });
	}

}
